﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Text;
using System.IO;

namespace Microsoft.Samples.ServiceHosting.HelloWorld
{
    public partial class ServiceSearch : System.Web.UI.Page
    {
        string previous_keyword;
        SqlConnection myConnection;


        private void Search_Keyword(string servicename)
        {
            //SqlCommand myCommand = new SqlCommand("SELECT SERVICENAME,SERVICEPROVIDER,SERVICETABLENAME FROM DBO.SERVICE WHERE SERVICENAME = '"+
            //                                        servicename+"';", myConnection);
            SqlCommand myCommand = new SqlCommand("SELECT ID,SERVICENAME,SERVICEPROVIDER,SERVICETABLENAME FROM DBO.SERVICE WHERE " +
                                                   "SERVICENAME LIKE '%" + servicename + "%';", myConnection);
            SqlDataReader myReader = null;
            try
            {
                myReader = myCommand.ExecuteReader();

                XElement x = new XElement("ServiceList");

                x.Add(new XElement("Services"));
                int service_number = 0;
                while (myReader.Read())
                {
                    XElement service = new XElement("Service");
                    service.Add(new XElement("ID", myReader["ID"].ToString()));
                    service.Add(new XElement("Name", myReader["SERVICENAME"].ToString()));
                    service.Add(new XElement("Provider", myReader["SERVICEPROVIDER"].ToString()));
                    x.Element("Services").Add(service);
                    service_number++;
                }
                //x.SetAttributeValue("ServiceNumber", service_number);
                myReader.Dispose();
                StringBuilder sb = new StringBuilder();
                x.Save(new StringWriter(sb));
                string xmlString = sb.ToString();
                string xmlStringChanged = xmlString.Replace("utf-16", "utf-8");
                Response.Write(xmlStringChanged);
                
            }
            catch (Exception b)
            {
                Trace.Write(b.ToString());
            }
        }

        private void Delete_Service(String ID)
        {
            SqlCommand myCommand = new SqlCommand("DELETE FROM DBO.SERVICE WHERE " +
                                                  "ID=" + ID + ";", myConnection);
            int row_affected = myCommand.ExecuteNonQuery();
            myCommand.Dispose();
            if (row_affected>0)
            {
                Search_Keyword(previous_keyword);
            }
           // myCommand.BeginExecuteNonQuery();
        }

        private void Update_Service(string ID, string service)
        {
            
        }

        private void Create_Service(string service, string description)
        {
            byte[] byteService = Encoding.UTF8.GetBytes(service);
            MemoryStream serviceStream = new MemoryStream(byteService);
            XElement xmlService = XElement.Load(serviceStream);
            string ID = (string)(xmlService.Element("ID"));
            string Name = (string)(xmlService.Element("Name"));
            string Provider = (string)(xmlService.Element("Provider"));

            SqlCommand myCommand = new SqlCommand("INSERT INTO DBO.SERVICE (ID,SERVICENAME,SERVICEPROVIDER,SERVICETABLENAME) VALUES ("+ID+"," +
                                                  "'" + Name + "'" + "," + "'" + Provider + "'" + "," + "'" + Name + "_Table" + "'" + ");", myConnection);

            try
            {
                int row_affected = myCommand.ExecuteNonQuery();
                myCommand.Dispose();
                if (row_affected > 0)
                {
                    Search_Keyword("");
                }
            }
            catch (Exception e)
            {
                Response.Write("?");
            }


            if (description != null)
            {
                byte[] byteDescription = Encoding.UTF8.GetBytes(description);
                MemoryStream descriptionStream = new MemoryStream(byteDescription);
                XElement ServiceContent = XElement.Load(descriptionStream);
                StringBuilder propertyList= new StringBuilder();
                foreach (XElement curElement in ServiceContent.Elements("Property"))
                {
                    propertyList.Append((string)(curElement.Element("Name")));
                    propertyList.Append(" ");
                    propertyList.Append((string)(curElement.Element("Type")));
                    propertyList.Append(",");
                }
                propertyList.Remove(propertyList.Length-1, 1);

                SqlCommand createCommand = new SqlCommand("CREATE TABLE DBO."+Name+"_Table" +
                                                        "("+ propertyList.ToString() +  ");", myConnection);

                createCommand.ExecuteNonQuery();
                createCommand.Dispose();
            }



        }

        protected void Page_Load(object sender, EventArgs e)
        {
            
            myConnection = new SqlConnection("user id=csci5221group05@vyda7dbsvv;" +
                                   "password=network@2011;server=vyda7dbsvv.database.windows.net;" +
                                   "Trusted_Connection=false;" +
                                   "database=mydatabase; " +
                                   "connection timeout=30");
            try
            {
                myConnection.Open();
            }
            catch (Exception a)
            {
                Trace.Write(a.ToString());
            }

            string servicename = Request.QueryString.Get("servicename");
            string action = Request.QueryString.Get("action");
            string ID = Request.QueryString.Get("ID"); 
            string service = Request.Form.Get("service"); 
            string description = Request.Form.Get("description"); 
            switch (action)
            {
                case "delete":
                    Delete_Service(ID);
                    break;
                case "search":
                    Search_Keyword(servicename);
                    break;
                case "update":
                    Update_Service(ID,service );
                    break;
                case "create":
                    Create_Service(service, description);
                    break;
                default:
                    break;
            }
            Response.Write("*");
            //Response.Write(servicename);
           
            

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string description = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
                                "<Description>" +
                                "<Property>" +
                                "<Name>aaa</Name>" +
                                "<Type>int</Type>" +
                                "</Property>" +
                                "</Description>";
            string service = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
                                "<Service>" +
                                "<Name>aaa</Name>" +
                                "<Provider>9</Provider>" +
                                "</Service>";
            Create_Service(service, description);
        }
    }
}